﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_BGM_StrategyDataMapping : IDAL_BGM_StrategyDataMapping
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public IList<M_BGM_StrategyDataMapping> GetFull(Nullable<int> bgm_year)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_StrategyDataMapping> infoList = new List<M_BGM_StrategyDataMapping>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);

                strSQL = "SELECT strmap.bgm_strat_data_map_seq, strmap.bgm_strat_map_seq"
                    + ", strmap.bgm_strat_list_seq, strmap.upper_bgm_strat_data_map_seq"
                    + ", strdata.bgm_strat_list_name, strdata.bgm_strat_type_seq"
                    + ", type.bgm_strat_type_name"

                    + ", strmap.record_status"
                    + " FROM bgm_strategy_data_mapping strmap"
                    + " LEFT JOIN bgm_strategy_data_list strdata ON strmap.bgm_strat_list_seq = strdata.bgm_strat_list_seq"
                    + " LEFT JOIN bgm_mas_strategy_type type ON strdata.bgm_strat_type_seq = type.bgm_strat_type_seq"
                    + " WHERE strmap.record_status not like @delete_status"
                    + (bgm_year != null ? " AND strmap.year_start <= @bgm_year AND strmap.year_end >= @bgm_year" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_StrategyDataMapping info = new M_BGM_StrategyDataMapping();
                        info.bgm_strat_data_map_seq = Convert.ToInt32(drc[i]["bgm_strat_data_map_seq"]);
                        info.bgm_strat_map_seq = cn.ConvertNullInt(drc[i]["bgm_strat_map_seq"]);
                        info.bgm_strat_list_seq = cn.ConvertNullInt(drc[i]["bgm_strat_list_seq"]);
                        info.upper_bgm_strat_data_map_seq = cn.ConvertNullInt(drc[i]["upper_bgm_strat_data_map_seq"]);
                        info.bgm_strat_type_seq = cn.ConvertNullInt(drc[i]["bgm_strat_type_seq"]);
                        info.m_bgm_strategydatalist.bgm_strat_list_name = drc[i]["bgm_strat_list_name"].ToString();
                        info.m_bgm_strategydatalist.m_bgm_strategytype.bgm_strat_type_name = drc[i]["bgm_strat_type_name"].ToString();
                        
                        info.record_status = drc[i]["record_status"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_StrategyDataMapping> GetRootList(Nullable<int> bgm_year)
        {
            string strSQL;
            //string strJoin = "";
            //string strWhere = "";
            DataRowCollection drc = null;
            IList<M_BGM_StrategyDataMapping> infoList = new List<M_BGM_StrategyDataMapping>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);

                strSQL = "SELECT distinct map0.bgm_strat_data_map_seq, map0.bgm_strat_map_seq"
                    + ", map0.bgm_strat_list_seq, null as upper_bgm_strat_data_map_seq, data.bgm_strat_list_name"
                    + ", data.bgm_strat_type_seq, type.bgm_strat_type_name"
                    + ", map0.record_status"
                    + " FROM bgm_strategy_data_mapping map0"
                    + " LEFT JOIN bgm_strategy_data_list data ON map0.bgm_strat_list_seq = data.bgm_strat_list_seq"
                    + " LEFT JOIN bgm_mas_strategy_type type ON data.bgm_strat_type_seq = type.bgm_strat_type_seq"
                    //+ strJoin
                    + " WHERE map0.record_status not like @delete_status"
                    + " AND type.is_root = 1"
                    + (bgm_year != null ? " AND map0.year_start <= @bgm_year AND map0.year_end >= @bgm_year" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_StrategyDataMapping info = new M_BGM_StrategyDataMapping();
                        info.bgm_strat_data_map_seq = Convert.ToInt32(drc[i]["bgm_strat_data_map_seq"]);
                        info.bgm_strat_map_seq = cn.ConvertNullInt(drc[i]["bgm_strat_map_seq"]);
                        info.bgm_strat_list_seq = cn.ConvertNullInt(drc[i]["bgm_strat_list_seq"]);
                        info.upper_bgm_strat_data_map_seq = cn.ConvertNullInt(drc[i]["upper_bgm_strat_data_map_seq"]);
                        info.bgm_strat_type_seq = cn.ConvertNullInt(drc[i]["bgm_strat_type_seq"]);
                        info.m_bgm_strategydatalist.bgm_strat_list_name = drc[i]["bgm_strat_list_name"].ToString();
                        info.m_bgm_strategydatalist.m_bgm_strategytype.bgm_strat_type_name = drc[i]["bgm_strat_type_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_StrategyDataMapping> GetList(Nullable<int> bgm_strat_data_map_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_StrategyDataMapping> infoList = new List<M_BGM_StrategyDataMapping>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_strat_data_map_seq", bgm_strat_data_map_seq);

                strSQL = "SELECT distinct map0.bgm_strat_data_map_seq, map0.bgm_strat_map_seq"
                    + ", map0.bgm_strat_list_seq, map0.upper_bgm_strat_data_map_seq, data.bgm_strat_list_name"
                    + ", lmap1.bgm_strat_data_map_seq as lower_bgm_strat_data_map_seq"
                    + ", data.bgm_strat_type_seq, type.bgm_strat_type_name"
                    + ", map0.record_status"
                    + " FROM bgm_strategy_data_mapping map0"
                    + " LEFT JOIN bgm_strategy_data_list data ON map0.bgm_strat_list_seq = data.bgm_strat_list_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping lmap1 ON map0.bgm_strat_data_map_seq = lmap1.upper_bgm_strat_data_map_seq"
                    + " LEFT JOIN bgm_mas_strategy_type type ON data.bgm_strat_type_seq = type.bgm_strat_type_seq"
                    + " WHERE map0.record_status not like @delete_status"
                    //+ " AND type.display = 1"
                    + (bgm_strat_data_map_seq != null ? " AND map0.upper_bgm_strat_data_map_seq = @bgm_strat_data_map_seq" : "")
                    + " ORDER BY map0.bgm_strat_data_map_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_StrategyDataMapping info = new M_BGM_StrategyDataMapping();
                        info.bgm_strat_data_map_seq = Convert.ToInt32(drc[i]["bgm_strat_data_map_seq"]);
                        info.bgm_strat_map_seq = cn.ConvertNullInt(drc[i]["bgm_strat_map_seq"]);
                        info.bgm_strat_list_seq = cn.ConvertNullInt(drc[i]["bgm_strat_list_seq"]);
                        info.upper_bgm_strat_data_map_seq = cn.ConvertNullInt(drc[i]["upper_bgm_strat_data_map_seq"]);
                        info.lower_bgm_strat_data_map_seq = cn.ConvertNullInt(drc[i]["lower_bgm_strat_data_map_seq"]);
                        info.bgm_strat_type_seq = cn.ConvertNullInt(drc[i]["bgm_strat_type_seq"]);
                        info.m_bgm_strategydatalist.bgm_strat_list_name = drc[i]["bgm_strat_list_name"].ToString();
                        info.m_bgm_strategydatalist.m_bgm_strategytype.bgm_strat_type_name = drc[i]["bgm_strat_type_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        //public IList<M_BGM_StrategyDataMapping> GetFull(Nullable<int> bgm_year, Nullable<int> level)
        //{
        //    string strSQL;
        //    string strJoin1 = "";
        //    string strJoin2 = "";
        //    string strWhere1 = "";
        //    string strWhere2 = "";
        //    DataRowCollection drc = null;
        //    IList<M_BGM_StrategyDataMapping> infoList = new List<M_BGM_StrategyDataMapping>();

        //    if (level != null)
        //    {
        //        for (int i = 0; i <= level; i++)
        //        {
        //            strJoin1 = strJoin1 + " LEFT JOIN bgm_strategy_data_mapping map" + (i + 1).ToString();
        //            strJoin1 = strJoin1 + " ON map" + (i).ToString() + ".bgm_strat_data_map_seq = map" + (i + 1).ToString() + ".upper_bgm_strat_data_map_seq";
        //        }

        //        strWhere1 = " AND map" + (level + 1).ToString() + ".bgm_strat_data_map_seq is null";
        //        strWhere2 = " AND map" + (level).ToString() + ".bgm_strat_data_map_seq is null";
        //    }

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(1, "@bgm_year", bgm_year);

        //        strSQL = "SELECT distinct map0.bgm_strat_data_map_seq, map0.bgm_strat_map_seq"
        //            + ", map0.bgm_strat_list_seq, map0.upper_bgm_strat_data_map_seq, data.bgm_strat_list_name"
        //            + ", map0.record_status"
        //            + " FROM bgm_strategy_data_mapping map0"
        //            + " LEFT JOIN bgm_strategy_data_list data ON map0.bgm_strat_list_seq = data.bgm_strat_list_seq"
        //            + strJoin
        //            + " WHERE map0.record_status not like @delete_status"
        //            + (bgm_year != null ? " AND data.year_start = @bgm_year" : "")
        //            + strWhere

        //            + " UNION ALL"
                    
        //            + " SELECT distinct map0.bgm_strat_data_map_seq, map0.bgm_strat_map_seq"
        //            + ", map0.bgm_strat_list_seq, map0.upper_bgm_strat_data_map_seq, data.bgm_strat_list_name"
        //            + ", map0.record_status"
        //            + " FROM bgm_strategy_data_mapping map0"
        //            + " LEFT JOIN bgm_strategy_data_list data ON map0.bgm_strat_list_seq = data.bgm_strat_list_seq"
        //            + strJoin
        //            + " WHERE map0.record_status not like @delete_status"
        //            + (bgm_year != null ? " AND data.year_start = @bgm_year" : "")
        //            + strWhere;

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_BGM_StrategyDataMapping info = new M_BGM_StrategyDataMapping();
        //                info.bgm_strat_data_map_seq = Convert.ToInt32(drc[i]["bgm_strat_data_map_seq"]);
        //                info.bgm_strat_map_seq = cn.ConvertNullInt(drc[i]["bgm_strat_map_seq"]);
        //                info.bgm_strat_list_seq = cn.ConvertNullInt(drc[i]["bgm_strat_list_seq"]);
        //                info.upper_bgm_strat_data_map_seq = cn.ConvertNullInt(drc[i]["upper_bgm_strat_data_map_seq"]);
        //                info.bgm_strat_list_name = drc[i]["bgm_strat_list_name"].ToString();

        //                info.record_status = drc[i]["record_status"].ToString();
        //                infoList.Add(info);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return infoList;
        //}

        public M_BGM_StrategyDataMapping GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_StrategyDataMapping info = new M_BGM_StrategyDataMapping();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT strmap.bgm_strat_data_map_seq, strmap.bgm_strat_map_seq"
                   + ", strmap.bgm_strat_list_seq, strmap.upper_bgm_strat_data_map_seq"
                   + ", strdata.bgm_strat_list_name"
                   + ", strdata.bgm_strat_type_seq, type.bgm_strat_type_name"

                   + ", strmap.record_status"
                   + " FROM bgm_strategy_data_mapping strmap"
                   + " LEFT JOIN bgm_strategy_data_list strdata ON plan.bgm_strategy_seq = strdata.bgm_strat_list_seq"
                   + " LEFT JOIN bgm_mas_strategy_type type ON strdata.bgm_strat_type_seq = type.bgm_strat_type_seq"
                    + " WHERE strmap.bgm_strat_data_map_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_strat_data_map_seq = Convert.ToInt32(drc[0]["bgm_strat_data_map_seq"]);
                    info.bgm_strat_map_seq = cn.ConvertNullInt(drc[0]["bgm_strat_map_seq"]);
                    info.bgm_strat_list_seq = cn.ConvertNullInt(drc[0]["bgm_strat_list_seq"]);
                    info.upper_bgm_strat_data_map_seq = cn.ConvertNullInt(drc[0]["upper_bgm_strat_data_map_seq"]);
                    info.bgm_strat_type_seq = cn.ConvertNullInt(drc[0]["bgm_strat_type_seq"]);
                    info.m_bgm_strategydatalist.bgm_strat_list_name = drc[0]["bgm_strat_list_name"].ToString();
                    info.m_bgm_strategydatalist.m_bgm_strategytype.bgm_strat_type_name = drc[0]["bgm_strat_type_name"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
    }
}
